package cn.kgc.dao.impl;

import cn.kgc.dao.BaseDao;
import cn.kgc.dao.TeacherDao;
import cn.kgc.entity.Teacher;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class TeacherImpl extends BaseDao implements TeacherDao {
//    查询方法和模糊查询
    @Override
    public List<Teacher> search(int currentPage,int currentSize,String jobnum,String name,String specialty) {
        String sql = null;
        String names = "%"+name+"%";
        String specialtys = "%"+specialty+"%";
        if((jobnum==null||"".equals(jobnum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){
            sql = "select * from school_teacher limit ?,?";
            return this.query(Teacher.class,sql,(currentPage-1)*currentSize,currentSize);
        }
        if((jobnum!=null&&!"".equals(jobnum))&&(name!=null&&!"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
            sql = "select * from school_teacher where jobnum=? and name like '"+names+"' and specialty like '"+specialtys+"' limit ?,?";
            return this.query(Teacher.class,sql,jobnum,(currentPage-1)*currentSize,currentSize);
        }
        if((jobnum!=null&&!"".equals(jobnum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){
            sql = "select * from school_teacher where jobnum=? limit ?,?";
            return this.query(Teacher.class,sql,jobnum,(currentPage-1)*currentSize,currentSize);
        }
        if((jobnum==null||"".equals(jobnum))&&(name!=null&&!"".equals(name))&&(specialty==null||"".equals(specialty))){
            sql = "select * from school_teacher where name like '"+names+"' limit ?,?";
            return this.query(Teacher.class,sql,(currentPage-1)*currentSize,currentSize);
        }
        if((jobnum==null||"".equals(jobnum))&&(name==null||"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
            sql = "select * from school_teacher where specialty like '"+specialtys+"' limit ?,?";
            return this.query(Teacher.class,sql,(currentPage-1)*currentSize,currentSize);
        }
        if((jobnum!=null&&name!=null&&!"".equals(jobnum)&&!"".equals(name))&&(specialty==null||"".equals(specialty))){
            sql = "select * from school_teacher where jobnum=? and name like '"+names+"' limit ?,?";
            return this.query(Teacher.class,sql,jobnum,(currentPage-1)*currentSize,currentSize);

        }
        if((jobnum!=null&&!"".equals(jobnum))&&(name==null||"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
            sql = "select * from school_teacher where jobnum=? and specialty like '"+specialtys+"' limit ?,?";
            return this.query(Teacher.class,sql,jobnum,(currentPage-1)*currentSize,currentSize);
        }
        if((jobnum==null||"".equals(jobnum))&&(name!=null&&specialty!=null&&!"".equals(name)&&!"".equals(specialty))){
            sql = "select * from school_teacher where name like '"+names+"' and specialty like '"+specialtys+"' limit ?,?";
            return this.query(Teacher.class,sql,(currentPage-1)*currentSize,currentSize);

        }
        return null;
    }
    //    计算总的记录数
    @Override
    public int totalCount(String jobnum,String name,String specialty) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int Count = 0;
        String sql = null;
        String names = "%"+name+"%";
        String specialtys = "%"+specialty+"%";
        try {
            con = this.getConnection();
            if((jobnum==null||"".equals(jobnum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){
                sql = "select Count(*) from school_teacher";
            }
            if((jobnum!=null&&!"".equals(jobnum))&&(name!=null&&!"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
                sql = "select Count(*) from school_teacher where jobnum=? and name like '"+names+"' and specialty like '"+specialtys+"'";
            }
            if((jobnum!=null&&!"".equals(jobnum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){
                sql = "select Count(*) from school_teacher where jobnum=?";
            }
            if((jobnum==null||"".equals(jobnum))&&(name!=null&&!"".equals(name))&&(specialty==null||"".equals(specialty))){
                sql = "select Count(*) from school_teacher where name like '"+names+"'";
            }
            if((jobnum==null||"".equals(jobnum))&&(name==null||"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
                sql = "select Count(*) from school_teacher where specialty like '"+specialtys+"'";
            }
            if((jobnum!=null&&name!=null&&!"".equals(jobnum)&&!"".equals(name))&&(specialty==null||"".equals(specialty))){
                sql = "select Count(*) from school_teacher where jobnum=? and name like '"+names+"'";
            }
            if((jobnum!=null&&!"".equals(jobnum))&&(name==null||"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
                sql = "select Count(*) from school_teacher where jobnum=? and specialty like '"+specialtys+"'";
            }
            if((jobnum==null||"".equals(jobnum))&&(name!=null&&specialty!=null&&!"".equals(name)&&!"".equals(specialty))){
                sql = "select Count(*) from school_teacher where name like '"+names+"' and specialty like '"+specialtys+"'";
            }
            ps = con.prepareStatement(sql);
            if(jobnum!=null&&!"".equals(jobnum)){
                ps.setObject(1,jobnum);
            }
            rs = ps.executeQuery();
            if(rs.next()){
                Count = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.closeAll(rs,ps,null,con);
        }
        return Count;
    }

    //计算总页数
    @Override
    public int totalPage(int pageSize,String jobnum,String name,String specialty){
        int totalCount = this.totalCount(jobnum,name,specialty);
        int CountPage = 0;
        if(totalCount%pageSize==0){
            CountPage = totalCount/pageSize;
        }else {
            CountPage = (totalCount/pageSize)+1;
        }
        return CountPage;
    }

    @Override
    public int add(Teacher teacher) {
        String sql = "INSERT INTO school_teacher (jobNum, name, gender, age, nation, birthdate, specialty, politicsStatus, jobTitle, address, phone)\n" +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        int Count = this.execute(sql,teacher.getJobnum(),teacher.getName(),teacher.getGender(),teacher.getAge(),teacher.getNation(),teacher.getBirthdate(),
                teacher.getSpecialty(),teacher.getPoliticsstatus(),teacher.getJobtitle(),teacher.getAddress(),teacher.getPhone());
        //Object[] params={};
        return Count;
    }

    //获取选中教师信息 进行回显
    @Override
    public Teacher getById(String ids) {
        String sql = " select * from school_teacher where id = ?";
        List<Teacher> list = this.query(Teacher.class, sql, ids);
        return list.get(0);
    }
    //修改教师信息
    @Override
    public int updateTeacher(Teacher teacher) {
        String sql = "update school_teacher set jobnum = ?,name = ?,gender = ?,age = ?,nation = ?,birthdate = ?,specialty = ?" +
                ",politicsstatus = ?,jobtitle = ?,address = ?,phone = ? where id = ?";
        Object[] params = {teacher.getJobnum(), teacher.getName(), teacher.getGender(), teacher.getAge(), teacher.getNation(), teacher.getBirthdate(),
                teacher.getSpecialty(), teacher.getPoliticsstatus(), teacher.getJobtitle(), teacher.getAddress(), teacher.getPhone(), teacher.getId()};
        return this.execute(sql, params);
    }


}
